package com.jessehu.xutilstest;

import android.annotation.TargetApi;
import android.os.Build;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

import com.jessehu.xutilstest.bean.PersonTable;
import com.jessehu.xutilstest.utils.DebugLog;

import org.xutils.DbManager;
import org.xutils.common.util.KeyValue;
import org.xutils.db.sqlite.SqlInfo;
import org.xutils.db.sqlite.WhereBuilder;
import org.xutils.db.table.DbModel;
import org.xutils.ex.DbException;
import org.xutils.view.annotation.Event;
import org.xutils.view.annotation.ViewInject;
import org.xutils.x;

import java.util.List;

public class DBTestActivity extends AppCompatActivity {

    DbManager.DaoConfig daoConfig = new DbManager.DaoConfig()
            .setDbName("test.db")
            // 不设置dbDir时, 默认存储在app的私有目录.
//            .setDbDir(new File(getExternalCacheDir().getAbsolutePath()))
            //设置数据库版本
            .setDbVersion(1)
            .setDbOpenListener(new DbManager.DbOpenListener() {
                @TargetApi(Build.VERSION_CODES.HONEYCOMB)
                @Override
                public void onDbOpened(DbManager db) {
                    // 开启WAL, 对写入加速提升巨大
                    db.getDatabase().enableWriteAheadLogging();
                }
            })
            .setDbUpgradeListener(new DbManager.DbUpgradeListener() {
                @Override
                public void onUpgrade(DbManager db, int oldVersion, int newVersion) {
                    //处理更新操作
                }
            });

    String[] sex = {"男", "女", "女", "男", "男", "男", "女", "男", "女", "男", "女", "女", "女"};
    int[] age = {32, 22, 21, 23, 43, 34, 25, 21, 40, 60, 21, 32, 55};
    int[] salary = {2000, 4000, 2500, 12000, 3200, 4300, 2300, 3000, 5000, 7000, 4200, 2200, 5400};

    @ViewInject(R.id.tv_db_result)
    private TextView dbResult;
    @ViewInject(R.id.btn_db_test)
    private Button dbBtn;

    private DbManager db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_dbtest);
        x.view().inject(this);
    }

    @Event(R.id.btn_db_test)
    private void onTestBtnClick(View view) {
        db = x.getDb(daoConfig);
//        insertData();
//        findDataById(2);
//        findFirstData();
//        findAllData();
//        findBySelector();
//        findDbModelFirstData();
//        findDbModelAllData();
//        updateDataById(1);
//        updateDataByWhere();
//        updateValuesByWhere();
//        deleteByid(1);
//        deleteByEntity();
//        clearTable();
//        deleteByWhere();
//        deleteTable();
//        deleteDb();
        addColumn();
    }

    /**
     * 插入
     */
    //插入数据
    private void insertData() {
        //插入数据
        PersonTable personTable = new PersonTable();
        for (int i = 0; i < sex.length; i++) {
            personTable.setName("name" + i);
            personTable.setAge(age[i]);
            personTable.setSalary(salary[i]);
            personTable.setSex(sex[i]);
            try {
                db.save(personTable);
//            dbManager.saveOrUpdate(personTable);
//            dbManager.saveBindingId(personTable);
            } catch (DbException e) {
                e.printStackTrace();
            }
        }
        DebugLog.i("insert", "insert success");

    }

    /**
     * 6种查找方式
     */
    //通过id查询数据
    private void findDataById(int id) {
        try {
            PersonTable person = db.findById(PersonTable.class, id);
            DebugLog.i("findDataById", person.toString());
        } catch (DbException e) {
            e.printStackTrace();
        }
    }

    //查找第一条数据
    private void findFirstData() {
        try {
            PersonTable person = db.findFirst(PersonTable.class);
            DebugLog.i("findFirstData", person.toString());
        } catch (DbException e) {
            e.printStackTrace();
        }
    }

    //查找所有的数据
    private void findAllData() {
        try {
            List<PersonTable> persons = db.findAll(PersonTable.class);
            DebugLog.i("findAllData", persons.toString());
        } catch (DbException e) {
            e.printStackTrace();
        }
    }

    //通过selector查找数据
    private void findBySelector() {
        try {
            List<PersonTable> persons = db.selector(PersonTable.class).where("age", "<", 30).and("sex", "=", "女")
                    .findAll();
            for (PersonTable person : persons) {
                DebugLog.i("findBySelector", person.toString());
            }
        } catch (DbException e) {
            e.printStackTrace();
        }
    }

    //查找第一条数据的某一个字段的值
    private void findDbModelFirstData() {
        try {
            DbModel model = db.findDbModelFirst(new SqlInfo("select * from person"));
            DebugLog.i("findDbModelFirstData", model.getString("age"));
        } catch (DbException e) {
            e.printStackTrace();
        }
    }

    //通过条件查找满足条件的所有字段的值
    private void findDbModelAllData() {
        try {
            List<DbModel> persons = db.findDbModelAll(new SqlInfo("select * from person where age > 25"));
            for (DbModel person : persons) {
                DebugLog.i("findDbModelAllData", person.getString("name"));
            }
        } catch (DbException e) {
            e.printStackTrace();
        }
    }

    /**
     * 3种修改方式
     */
    //通过该ID修改
    private void updateDataById(int id) {
        try {
            PersonTable person = db.findById(PersonTable.class, id);
            person.setAge(35);
            db.update(person, "age");
        } catch (Exception e) {
            e.printStackTrace();
        }
        findAllData();
    }

    //通过条件修改某个键的值
    private void updateDataByWhere() {
        try {
            db.update(PersonTable.class, WhereBuilder.b("sex", "=", "男").and("age", "=", 23), new KeyValue("salary",
                    10000));
        } catch (Exception e) {
            e.printStackTrace();
        }
        findAllData();
    }

    //通过条件修改多个键的值
    private void updateValuesByWhere() {
        try {
            db.update(PersonTable.class, WhereBuilder.b("sex", "=", "男").and("age", ">", 23), new KeyValue[]{new
                    KeyValue("name", "name111"), new KeyValue("salary", 10000)});
        } catch (Exception e) {
            e.printStackTrace();
        }
        findAllData();
    }

    /**
     * 6种删除方式
     */
    //通过id删除
    private void deleteByid(int id) {
        try {
            db.deleteById(PersonTable.class, id);
        } catch (DbException e) {
            e.printStackTrace();
        }
        findAllData();
    }

    //根据条件删除
    private void deleteByEntity(){
        try {
            PersonTable person = db.selector(PersonTable.class).where("name", "=", "name1").findFirst();
            db.delete(person);
        } catch (DbException e) {
            e.printStackTrace();
        }
        findAllData();
    }

    //清空表
    private void clearTable(){
        try {
            db.delete(PersonTable.class);
        } catch (DbException e) {
            e.printStackTrace();
        }
        insertData();
        findAllData();
    }

    //指定条件删除
    private void deleteByWhere(){
        try {
            db.delete(PersonTable.class, WhereBuilder.b("sex", "=", "男").and("salary", "=", "2000"));
        } catch (DbException e) {
            e.printStackTrace();
        }
        findAllData();
    }

    //删除表
    private void deleteTable() {
        try {
            db.dropTable(PersonTable.class);
        } catch (DbException e) {
            e.printStackTrace();
        }
    }

    //删除数据库
    private void deleteDb(){
        try {
            db.dropDb();
        } catch (DbException e) {
            e.printStackTrace();
        }
    }

    /**
     * 增加字段
     */
    //添加字段
    private void addColumn() {
        try {
            db.addColumn(PersonTable.class, "country");
        } catch (DbException e) {
            e.printStackTrace();
        }
        findAllData();
    }
}
